package com.lagou.webwork.dao;

import com.lagou.webwork.model.Class;
import com.lagou.webwork.model.ListResult;
import com.lagou.webwork.model.Student;
import com.lagou.webwork.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @author sjp
 * @date 2021/6/4 15:39
 * @description
 */
public class ClassDaoImp implements ClassDao {

    /**
     * 获取班级列表
     *
     * @param paramsMap
     * @param startPosition
     * @param currentCount
     * @return
     */
    @Override
    public ListResult<Class> getClassList(List<Map<String, Object>> paramsMap, int startPosition, int currentCount) {
        try {
            //1.创建QueryRunner对象,使用Druid自动管理
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL语句
            StringBuilder sql = new StringBuilder();
            sql.append("select * from class ");

            //用于计算总数
            StringBuilder sql2 = new StringBuilder();
            sql2.append("select count(1) from class ");

            //3.拼接查询条件
            List<Class> queryList = null;
            int totalCount = 0;
            if (null != paramsMap && paramsMap.size() > 0) {

                List<String> keyList = new ArrayList<>();
                List<String> valueList = new ArrayList<>();

                for (Map<String, Object> map : paramsMap) {
                    keyList.add((String) map.get("key"));
                    valueList.add((String) map.get("value"));
                }


                sql.append(" where ");
                sql2.append(" where ");
                List<Object> paramsList = new ArrayList<>();
                for (int i = 0; i < keyList.size(); i++) {
                    if (0 < i) {
                        sql.append(" and ");
                        sql2.append(" and ");
                    }
                    sql.append(keyList.get(i) + " like ? ");
                    sql2.append(keyList.get(i) + " like ? ");
                    paramsList.add("%" + valueList.get(i) + "%");
                }

                Object[] paramsObjectArr = paramsList.toArray();

                totalCount = queryRunner.query(sql2.toString(), new ScalarHandler<Long>(), paramsObjectArr).intValue();
                sql.append(" limit ?,? ");

                paramsList.add(startPosition);
                paramsList.add(currentCount);
                paramsObjectArr = paramsList.toArray();

                queryList = queryRunner.query(sql.toString(), new BeanListHandler<Class>(Class.class), paramsObjectArr);
            } else {
                //4.执行sql
                totalCount = queryRunner.query(sql2.toString(), new ScalarHandler<Long>()).intValue();
                sql.append(" limit ?,? ");
                queryList = queryRunner.query(sql.toString(), new BeanListHandler<Class>(Class.class), startPosition, currentCount);
            }
            return new ListResult<>(queryList, totalCount);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 添加班级
     * @param addClass
     * @return
     */
    @Override
    public ListResult<Class> addClass(Class addClass) {
        //1.创建QueryRunner对象,使用Druid自动管理
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //2.编写SQL语句
        String sql = "INSERT INTO class VALUES (null, ?, ?, ?, ?, ?)";

        try {
            //3.执行SQL语句
            int insert = queryRunner.update(sql, addClass.getName(), addClass.getGrade(), addClass.getClassMaster(), addClass.getRemark(), addClass.getNumber());

            if (insert > 0) {
                List<Class> list = new ArrayList<>();
                list.add(addClass);
                return new ListResult<>(list, 1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return new ListResult<>(null, 0);
    }

    /**
     * 批量删除班级信息
     *
     * @param idList
     * @return
     */
    @Override
    public ListResult<Integer> deleteClassList(List<Integer> idList) {
        try {
            //1.创建QueryRunner对象,使用Druid自动管理
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL语句
            String paramsString = Arrays.toString(idList.toArray());
            paramsString = paramsString.substring(1, paramsString.length() - 1);
            String sql = "delete from class where id in ("+ paramsString+")";

            //3.执行SQL语句
            int update = queryRunner.update(sql);
            if (update>0){
                return new ListResult<>(idList, 1);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return new ListResult<>(null, 0);
    }

    /**
     * 根据Id获取班级信息
     * @param id
     * @return
     */
    @Override
    public ListResult<Class> getClassModelById(int id) {
        //1.创建QueryRunner对象,使用Druid自动管理
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //2.编写SQL语句
        String sql = "select * from class where id = ?";
        try {
            //3.执行SQL语句
            Class classModel = queryRunner.query(sql, new BeanHandler<>(Class.class), id);
            if (classModel != null) {
                List<Class> classList = new ArrayList<>();
                classList.add(classModel);
                return new ListResult<>(classList, 1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new ListResult<>(new ArrayList<>(), 0);
    }

    /**
     * 更新班级信息
     * @param updateClass
     * @return
     */
    @Override
    public ListResult<Class> updateClass(Class updateClass) {
        try {
            //1.创建QueryRunner对象,使用Druid自动管理
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL语句
            String sql = "update class set name = ? , grade = ? , classmaster = ? , remark = ?  where id = ?";

            //3.执行SQL语句
            int update = queryRunner.update(sql, updateClass.getName(), updateClass.getGrade(), updateClass.getClassMaster()
                    , updateClass.getRemark(),updateClass.getId());

            if (update > 0) {
                List<Class> list = new ArrayList<>();
                list.add(updateClass);
                return new ListResult<>(list, 1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new ListResult<>(null, 0);
    }

    /**
     * 判断当前班级是否存在学生
     * @param id
     * @return
     */
    @Override
    public boolean isExistStudent(int id) throws SQLException {

        //1.创建QueryRunner
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select count(1) from student join class on student.classid = class.id where class.id = ?";
        int studentCount = runner.query(sql, new ScalarHandler<Long>(), id).intValue();
        if (studentCount>0){
            return true;
        }
        return false;
    }
}
